﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using VswService_V7.Utility;

namespace VswService_V7.DataBase
{
    public static class OracleHelper
    {
        //数据库连接字
        public static OracleConnectionStringBuilder oracleConnectionStringBuilder;

        /// <summary>
        /// 用于ORACLE POOL 初始化连接
        /// </summary>
        /// <param name="stringBuilder"></param>
        /// <returns></returns>
        public static bool InitialConnectPool(OracleConnectionStringBuilder stringBuilder)
        {
            oracleConnectionStringBuilder = stringBuilder;
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                try
                {
                    conn.Open();
                    return true;
                }
                catch
                {
                    conn.Close();
                    conn.Dispose();
                    return false;
                }

            }
        }

        #region public methods

        /// <summary>
        /// 获取序列的下一个值
        /// </summary>
        /// <returns>EventNo</returns>
        public static long GetNextEventNo()
        {
            string sqlText = "select EVENTSEQ.nextval from dual";
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(sqlText, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        object obj = cmd.ExecuteScalar();
                        if (obj != null)
                            return long.Parse(obj.ToString());
                        else
                            return -1;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }


        /// <summary>
        /// 执行简单查询语句
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns>-1 代表失败</returns>
        public static int Exct_Scalar(string sqlText)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(sqlText, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        object obj = cmd.ExecuteScalar();
                        if (obj != null)
                            return Convert.ToInt32(obj);
                        else
                            return -1;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        public static object Exct_ScalarObj(string sqlText)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(sqlText, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        object obj = cmd.ExecuteScalar();
                        if (obj != null)
                            return obj;
                        else
                            return null;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }


        /// <summary>
        /// 执行scalar 查询
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public static object GetSingle(string SQLString)
        {

            try
            {
                using (OracleConnection connection = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
                {
                    using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return "";
                        }
                        else
                        {
                            return obj;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("GetSingle", ex);
                throw ex;

            }
        }

        /// <summary>
        /// 执行简单查询语句
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int Exct_NonQuery(string sqlText)
        {
            int result = 0;
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(sqlText, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        LogHelper.WriteLog("Exct_NonQuery", ex);
                        throw ex;
                    }
                }
            }
        }



        /// <summary>
        /// 带参数使用读取器SqlDataReader 读取记录
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static OracleDataReader Exct_Reader(string commandText, OracleParameter[] parameters)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    try
                    {
                        if (parameters != null)
                            cmd.Parameters.AddRange(parameters);
                        OracleDataReader reader = cmd.ExecuteReader();
                        return reader;
                    }
                    catch (Exception ex)
                    {
                        LogHelper.WriteLog("Exct_Reader", ex);
                        throw ex;
                    }
                }
            }

            //if (dtr.Read())
            //{
            //    address = dtr["SvjDataAddress"].ToString();
            //    // 修改数据
            //    // string sqlChangeStatus = "update cadqueue q  set q.status = :status  Where CadqueueId = :CadqueueId";
            //    // cmd.CommandText = sqlChangeStatus;
            //    // cmd.Parameters.Clear();
            //    // cmd.Parameters.Add("CadqueueId", cadqueueId);
            //    // cmd.ExecuteNonQuery();
            //}
            //dtr.Close();
            //cn.Close();
        }

        /// <summary>
        /// 不带参数使用读取器SqlDataReader 读取记录
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static OracleDataReader Exct_Reader(string commandText)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    try
                    {
                        OracleDataReader reader = cmd.ExecuteReader();
                        return reader;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

            //if (dtr.Read())
            //{
            //    address = dtr["SvjDataAddress"].ToString();
            //    // 修改数据
            //    // string sqlChangeStatus = "update cadqueue q  set q.status = :status  Where CadqueueId = :CadqueueId";
            //    // cmd.CommandText = sqlChangeStatus;
            //    // cmd.Parameters.Clear();
            //    // cmd.Parameters.Add("CadqueueId", cadqueueId);
            //    // cmd.ExecuteNonQuery();
            //}
            //dtr.Close();
            //cn.Close();
        }

        /// <summary>
        /// 执行多条SQL语句，实现insert update delete数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static void ExecuteSqlTran(List<string> sqlList)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction trans = conn.BeginTransaction();
                cmd.Transaction = trans;
                try
                {
                    for (int n = 0; n < sqlList.Count; n++)
                    {
                        string strsql = sqlList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    //“ORA-00001: 违反唯一约束条件 (WHJ.KEYTABLE_PK)”
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 在同一个连结中同时执行多条增加 SQL 语句，但不使用事务。在频繁插入时提高效率。
        /// </summary>
        public static int ExecuteSqlList(string sqlText)
        {
            //需要传入的sql 语句格式如下
            //string sql = "INSERT ALL " +
            //    "INTO KEYTABLE1(para1,...,paran) VALUES(v1,...,'vn') " +
            //    "INTO KEYTABLE2(para1,...,paran) VALUES(v1,...,'vn') " +
            //    "INTO KEYTABLE3(para1,...,paran) VALUES(v1,...,'vn') " +
            //    "SELECT 1 FROM DUAL";

            int r = ExecuteNonQuery(sqlText);
            return r;
        }


        /// <summary>
        /// 查询一张sql 语句指定的表格
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static DataTable Exct_QueryTable(string sqlText)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
                {
                    conn.Open();
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sqlText;
                        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                        DataTable datatable = new DataTable();
                        adapter.Fill(datatable);
                        return datatable;
                        //if (datatable.Rows.Count > 0)
                        //    return datatable;
                        //else
                        //    return null;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Exct_QueryTable", ex);
                throw ex;
            }

        }

        /// <summary>
        /// 执行带参数过程
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int Procedure_Parameter(string commandText, OracleParameter[] parameters)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    try
                    {
                        if (parameters != null)
                            cmd.Parameters.AddRange(parameters);
                        int result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

        }

        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int Procedure_NoParameter(string commandText)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(commandText, conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    try
                    {
                        int result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

        }


        #endregion



        #region not used methods

        /// <summary>
        /// 以事务的方式向一张表格中插入一个data table
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static bool Trans_InsertTable(string tableName, DataTable table)
        {
            return false;

            //string selectOrderSQL = "select * from " + tableName;
            //using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            //{
            //    conn.Open();
            //    using (OracleTransaction trans = conn.BeginTransaction())
            //    {
            //        using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
            //        {
            //            dataAdapter的SelectCommand中检索SQL文设定
            //            dataAdapter.SelectCommand = new OracleCommand(selectOrderSQL, conn);
            //            根据SelectCommand中检索SQL文，OracleCommandBuilder 将自动生成dataAdapter相应的
            //            UPDATE, INSERT, DELETE 用SQL文，并且oracle paramter也相应填入。
            //            OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
            //            try
            //            {
            //                遍历ORDER TABLE 将所有值状态改为 insert
            //                foreach (DataRow dr in table.Rows)
            //                {
            //                    if (dr.RowState != DataRowState.Added)
            //                        dr.SetAdded();
            //                }
            //                int rCount = dataAdapter.Update(table);//数据更新 
            //                每次只能增加一条ORDER
            //                if (rCount < 1)
            //                {
            //                    trans.Rollback();
            //                    return false;
            //                }

            //            }
            //            catch (Exception ex)
            //            {
            //                trans.Rollback();
            //                trans.Commit();
            //                throw ex;
            //            }
            //            table.AcceptChanges();
            //            trans.Commit();
            //            return true;
            //        }
            //    }
            //}
        }


        #endregion


        #region  comments

        //1.ExecuteNonQuery() 不执行存储过程.
        //此时如果对数据库执行,插入,更新,删除操作,返回的是 受影响的行数.(及一个大于等于0的整数)

        //2.ExecuteNonQuery() 执行查询不返回影响的行数.

        //3.ExecuteNonQuery   执行存储过程
        //[1].存储过程有返回值(传出参数) 把数据库中受影响的行数赋给返回值,这是得到的返回值是受影响的函数(大于或等于0的整数).
        //[2].存储过程没有返回值 执行成功后返回 -1.

        #endregion




        #region REFERENCE METOD
        //1.ExecuteNonQuery() 不执行存储过程.
        //此时如果对数据库执行,插入,更新,删除操作,返回的是 受影响的行数.(及一个大于等于0的整数)

        //2.ExecuteNonQuery() 执行查询不返回影响的行数.

        //3.ExecuteNonQuery   执行存储过程
        //[1].存储过程有返回值(传出参数) 把数据库中受影响的行数赋给返回值,这是得到的返回值是受影响的函数(大于或等于0的整数).
        //[2].存储过程没有返回值 执行成功后返回 -1.

        /// <summary>
        /// 执行简单查询语句
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private static int ExecuteNonQuery(string sqlText)
        {
            int result = 0;
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(sqlText, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        return -1;
                    }
                }
            }
        }

        private static int ExecuteSql(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        private static DataTable Returntable(string sql)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    return datatable;
                }
            }
        }

        private static DataTable Returntable(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    return datatable;
                }
            }
        }
        #endregion

        #region help code

        /// <summary>
        /// 创建data table 示例
        /// </summary>
        public class HelpCode
        {
            public static DataTable CreateDataTable()
            {
                DataTable dt = new DataTable("cart");
                DataColumn dc1 = new DataColumn("prizename", Type.GetType("System.String"));
                DataColumn dc2 = new DataColumn("point", Type.GetType("System.Int16"));
                DataColumn dc3 = new DataColumn("number", Type.GetType("System.Int16"));
                DataColumn dc4 = new DataColumn("totalpoint", Type.GetType("System.Int64"));
                DataColumn dc5 = new DataColumn("prizeid", Type.GetType("System.String"));
                dt.Columns.Add(dc1);
                dt.Columns.Add(dc2);
                dt.Columns.Add(dc3);
                dt.Columns.Add(dc4);
                dt.Columns.Add(dc5);
                //以上代码完成了DataTable的构架，但是里面是没有任何数据的
                for (int i = 0; i < 10; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["prizename"] = "kakaxi";
                    dr["point"] = 10;
                    dr["number"] = 1;
                    dr["totalpoint"] = 10;
                    dr["prizeid"] = "001";
                    dt.Rows.Add(dr);
                }

                return dt;
            }
        }



        /// <summary>
        /// data row to 实例，测试中
        /// </summary>
        public class Mapper
        {
            /// <summary>
            /// data row to object 实例
            /// </summary>
            /// <param name="adaptedRow"></param>
            /// <param name="entityType"></param>
            /// <returns></returns>
            public static object ToEntity(DataRow adaptedRow, Type entityType)
            {
                if (entityType == null || adaptedRow == null)
                {
                    return null;
                }

                object entity = Activator.CreateInstance(entityType);
                CopyToEntity(entity, adaptedRow);
                return entity;
            }

            /// <summary>
            /// data row to 泛型实例
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="adaptedRow"></param>
            /// <param name="value"></param>
            /// <returns></returns>
            public static T ToEntity<T>(DataRow adaptedRow, T value) where T : new()
            {
                T item = new T();
                if (value == null || adaptedRow == null)
                {
                    return item;
                }

                item = Activator.CreateInstance<T>();
                CopyToEntity(item, adaptedRow);
                return item;
            }


            public static void CopyToEntity(object entity, DataRow adaptedRow)
            {
                if (entity == null || adaptedRow == null)
                {
                    return;
                }
                PropertyInfo[] propertyInfos = entity.GetType().GetProperties();

                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    if (!CanSetPropertyValue(propertyInfo, adaptedRow))
                    {
                        continue;
                    }

                    try
                    {
                        if (adaptedRow[propertyInfo.Name] is DBNull)
                        {
                            propertyInfo.SetValue(entity, null, null);
                            continue;
                        }
                        SetPropertyValue(entity, adaptedRow, propertyInfo);
                    }
                    finally
                    {

                    }
                }
            }


            private static bool CanSetPropertyValue(PropertyInfo propertyInfo, DataRow adaptedRow)
            {
                if (!propertyInfo.CanWrite)
                {
                    return false;
                }

                if (!adaptedRow.Table.Columns.Contains(propertyInfo.Name))
                {
                    return false;
                }

                return true;
            }


            private static void SetPropertyValue(object entity, DataRow adaptedRow, PropertyInfo propertyInfo)
            {
                if (propertyInfo.PropertyType == typeof(DateTime?) ||
                    propertyInfo.PropertyType == typeof(DateTime))
                {
                    DateTime date = DateTime.MaxValue;
                    DateTime.TryParse(adaptedRow[propertyInfo.Name].ToString(),
                       CultureInfo.CurrentCulture, DateTimeStyles.None, out date);

                    propertyInfo.SetValue(entity, date, null);
                }
                else
                {
                    propertyInfo.SetValue(entity, adaptedRow[propertyInfo.Name], null);
                }
            }

        }

        public class ConvertToEntity<T> where T : new()
        {
            public static T ConvertDataRowToModel(DataRow row)
            {
                T t = new T();
                Type type = t.GetType();
                foreach (PropertyInfo pi in type.GetProperties())
                {
                    if (row.Table.Columns.Contains(pi.Name))
                    {
                        if (!pi.CanWrite)
                            continue;


                        object rowValue = row[pi.Name];
                        if (rowValue.GetType() == typeof(Int64))
                        {
                            rowValue = Convert.ToInt32(rowValue);
                        }


                        if (rowValue != DBNull.Value)
                        {
                            try
                            {
                                pi.SetValue(t, rowValue, null);
                            }
                            catch (Exception e)
                            {
                                //LogHelper.Error(null, e.Message);
                                throw e;
                            }
                        }
                    }
                }


                return t;
            }
        }

        #endregion
    }
}
